package com.xframe.xdal.core;


import com.alibaba.druid.pool.DruidDataSource;
import com.xframe.xdal.core.constant.*;
import com.xframe.xdal.core.handler.ResultSetHandler;
import com.xframe.xdal.core.manage.BaseDbManage;
import com.xframe.xdal.core.param.ParamContext;
import com.xframe.xdal.core.tran.*;
import com.xframe.xdal.core.util.*;
import lombok.AccessLevel;
import lombok.Data;
import lombok.Setter;
import lombok.extern.log4j.Log4j2;

import java.sql.*;
import java.util.List;

/**
 * 功能描述:使用阿里连接池来执行SQL语句
 *
 * @author xufeng
 * @version 1.0
 * 2022-09-26
 */
@Data
@Log4j2
public class XDataSource {


    /**
     * DataSource 实例阿里的连接池
     */
    @Setter(AccessLevel.NONE)
    private DruidDataSource dataSource;

    /**
     * DataSource 数据库类型
     */
    @Setter(AccessLevel.NONE)
    private DataSourceTypeEnums dataSourceTypeEnums;


    /**
     * 数据源名称
     */
    @Setter(AccessLevel.NONE)
    private String dataSourceName;
    /**
     * ip
     */
    @Setter(AccessLevel.NONE)
    private String host;
    /**
     * 端口
     */
    @Setter(AccessLevel.NONE)
    private Integer port;
    /**
     * 数据库用户名
     */
    @Setter(AccessLevel.NONE)
    private String username;
    /**
     * 数据库密码
     */
    @Setter(AccessLevel.NONE)
    private String password;
    /**
     * 数据库名称
     */
    @Setter(AccessLevel.NONE)
    private String databaseName;


    /**
     * 构架名
     */
    @Setter(AccessLevel.NONE)
    private String schema;
    /**
     * 数据库连接模板
     */
    @Setter(AccessLevel.NONE)
    private String jdbcUrl;


    /**
     * 数据库管理对象
     */
    @Setter(AccessLevel.NONE)
    private BaseDbManage dbManage;

    @Setter(AccessLevel.NONE)
    private ParamContext paramContext;


    public Object testConnect() throws SQLException {
        return this.executeScalar(this.getValidationQuery(),null);
    }
    /**
     * 构造数据源
     *
     * @param dataSourceTypeEnums 数据库类型 枚举
     * @param dataSourceName      数据源名称
     * @param host                ip
     * @param port                端口号
     * @param username            用户名
     * @param password            密码
     * @param databaseName        数据库名称
     * @param schema              架构名称
     */
    public XDataSource(DataSourceTypeEnums dataSourceTypeEnums, String dataSourceName,
                       String host, Integer port, String username,
                       String password, String databaseName, String schema) {

        this.dataSourceTypeEnums = dataSourceTypeEnums;
        this.dataSourceName = dataSourceName;
        this.host = host;
        this.port = port;
        this.username = username;
        this.password = password;
        this.databaseName = databaseName;
        this.schema = schema;

        this.jdbcUrl = StringUtil.format(getJdbcTemplate(this.dataSourceTypeEnums), this.host, this.port, this.databaseName);

        this.paramContext = new ParamContext(this.dataSourceTypeEnums);
        this.dbManage = BaseDbManage.create(this);

    }




    /**
     * 根据数据库类型返回 jdbc 连接模板
     *
     * @param dataSourceTypeEnums 数据库枚举
     * @return string
     */
    private String getJdbcTemplate(DataSourceTypeEnums dataSourceTypeEnums) {
        switch (dataSourceTypeEnums) {
            case MySql:
                return DataSourceConstant.MYSQL_URL_PATTERN;
            case Oracle:
                return DataSourceConstant.ORACLE_URL_PATTERN;
            case SqlServer:
                return DataSourceConstant.SQLSERVER_URL_PATTERN;
            case ClickHouse:
                return DataSourceConstant.CLICK_HOUSE_URL_PATTERN;
        }
        return DataSourceConstant.MYSQL_URL_PATTERN;
    }

    /**
     * 获取检验连接sql语句
     *
     * @return sql 语句
     */
    private String getValidationQuery() {
        switch (dataSourceTypeEnums) {
            case Oracle:
                return "select sysdate from dual";
            case MySql:
                return "SELECT NOW()";
            case SqlServer:
                return "SELECT getdate()";
            case ClickHouse:
                return "SELECT NOW()";
        }
        return "select 1";
    }


    /**
     * 初始化数据源
     */
    public void init() {
        if (dataSource == null) {
            dataSource = new DruidDataSource();
            dataSource.setName(dataSourceName);
            dataSource.setUrl(jdbcUrl);
            dataSource.setUsername(this.getUsername());
            dataSource.setPassword(this.getPassword());
            dataSource.setValidationQuery(getValidationQuery());
            dataSource.setTestOnBorrow(false);
            dataSource.setTestOnReturn(false);
            dataSource.setInitialSize(2);
            dataSource.setMaxActive(1000);
            dataSource.setConnectionErrorRetryAttempts(0);
            dataSource.setBreakAfterAcquireFailure(true);
            dataSource.setRemoveAbandoned(true);
            dataSource.setRemoveAbandonedTimeout(1800);
            dataSource.setLogAbandoned(true);

            try {
                dataSource.init();
                //dataSource.isEnable();
            } catch (SQLException ex) {
                log.error(ex);
                dataSource.close();
            }
        }

    }

    /**
     * 销毁
     *
     * @return 是否成功
     */
    public boolean destroy() {
        if (null == this.dataSource) {
            return true;
        }
        this.dataSource.close();
        this.dataSource = null;
        return true;
    }

    /**
     * 开始一个事务对象(数据库默认隔离级别)
     *
     * @return IDbTransaction 对象
     */
    public IDbTransaction beginTransaction(){
        try {
            Connection connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            IDbTransaction transaction =  DbTransactionFactory.createTran(connection);
            DbTransactionContext.addTransaction(transaction);
            return transaction;
        }catch (Exception exception){
            log.info(exception.getMessage());
            return null;
        }
    }
    /**
     * 提交一个事务
     */
    public void commitTran(){
        if(DbTransactionContext.hasDbTransaction()){
            IDbTransaction transaction = DbTransactionContext.getTrans();
            try {
                transaction.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                closeTransaction(transaction);
            }

        }
    }
    /**
     * 回滚事务
     */
    public void rollBackTran(){
        if(DbTransactionContext.hasDbTransaction()){
            IDbTransaction transaction = DbTransactionContext.getTrans();
            transaction.rollBack();
            closeTransaction(transaction);
        }
    }

    /**
     * 开始一个事务对象,并制定数据库默认隔离级别
     *
     * @return IDbTransaction 对象
     */
    public IDbTransaction beginTransaction(int level) {
        try {
            Connection connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            connection.setTransactionIsolation(level);
            IDbTransaction transaction = DbTransactionFactory.createTran(connection);
            DbTransactionContext.addTransaction(transaction);
            return transaction;
        }catch (Exception exception){
            log.info(exception.getMessage());
            return null;
        }
    }

    /**
     * 关闭事务
     *
     * @param transaction 事务
     */
    public void closeTransaction(IDbTransaction transaction){
        DbTransactionContext.remove();
        transaction.close();
    }

    /**
     * 执行一个Count查询，只返回第一行第一列的数据
     *
     * @param querySql 查询sql语句
     * @param params   查询对应的参数
     * @return long      返回结果
     */
    public Long executeCount(String querySql, List<Object> params) throws SQLException {
        log.info(querySql);
        Object result = executeScalar(querySql, params);
        if (result instanceof Long) {
            return (Long) result;
        }
        try {
            return Long.valueOf(result.toString());
        } catch (Exception exception) {
            exception.printStackTrace();
            return 0L;
        }
    }




    private Connection getCurConnection() throws SQLException {
        if(DbTransactionContext.hasDbTransaction()){
            IDbTransaction transaction = DbTransactionContext.getTrans();
            return transaction.getConnection();
        }
        return dataSource.getConnection();
    }

    private Connection getNewCurConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 执行一个查询，只返回第一行第一列的数据
     *
     * @param querySql sql查询语句
     * @param params   查询参数
     * @return Object  返回对象
     */
    public Object executeScalar(String querySql, List<Object> params) throws SQLException {
        Connection conn = null;
        PreparedStatement pst = null;
        Object result = null;
        ResultSet resultSet = null;
        try {
            conn = getCurConnection();
            pst = conn.prepareStatement(querySql);
            if (params != null) {
                setParam(pst, params);
            }
            if (log.isDebugEnabled()) {
                log.info(querySql);
            }
            resultSet = pst.executeQuery();
            while (resultSet.next()) {
                result = resultSet.getObject(1);
                resultSet.close();
                break;
            }
        } catch (SQLException e) {
            log.error("", e);
            throw e;
        } finally {
            close(resultSet);
            close(pst);
            closeConnection(conn);
        }
        return result;
    }


    /**
     * 更新
     *
     * @param updateSql sql语句
     * @param params    参数
     * @return 影响行数
     * @throws SQLException 异常
     */
    public int executeUpdate(String updateSql, List<Object> params) throws SQLException {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getCurConnection();
            pst = conn.prepareStatement(updateSql);
            if (params != null) {
                setParam(pst, params);
            }
            log.info(updateSql);
            return pst.executeUpdate();
        } catch (SQLException exception) {
            exception.printStackTrace();
            throw exception;
        } finally {
            close(pst);
            closeConnection(conn);
        }
    }

    /**
     * 更新
     *
     * @param updateSql sql语句
     * @param params    参数
     * @return 影响行数
     * @throws SQLException 异常
     */
    public int executeUpdateNoTran(String updateSql, List<Object> params) throws SQLException {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getNewCurConnection();
            pst = conn.prepareStatement(updateSql);
            if (params != null) {
                setParam(pst, params);
            }
            log.info(updateSql);
            return pst.executeUpdate();
        } catch (SQLException exception) {
            exception.printStackTrace();
            throw exception;
        } finally {
            close(pst);
            closeConnection(conn);
        }
    }


    /**
     * 批量执行语句 只测试了oracle
     * @param updateSql 更新语句 末尾以“;”结尾
     * @param params 参数集合
     * @return 影响行数数组
     */
    public int[] executeBatch(String updateSql, List<Object> params) throws SQLException {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = this.getCurConnection();
            // 每一个执行得sql 语句后边都带分号
            String[] split = updateSql.split(DalConstants.SQL_END);
            // 总执行条数
            int count = split.length;
            int paramCount = 0;
            if (params != null) {
                paramCount = params.size() / count;
            }
            pst = conn.prepareStatement(split[0]);
            for (int index = 0; index < count; index++) {
                if (params != null) {
                    setParam(pst,params.subList(index * paramCount, (index + 1) * paramCount));
                }
                // 有无参数都加入批量执行语句
                pst.addBatch();
            }
            if(log.isDebugEnabled()){
                log.info(split[0]);
            }
            return pst.executeBatch();
        } catch (SQLException exception) {
            exception.printStackTrace();
            throw exception;
        } finally {
            close(pst);
            closeConnection(conn);
        }
    }


    /**
     * 执行一个查询SQL
     *
     * @param querySql sql语句
     * @param params    参数
     * @param resultSetHandler    返回结果处理器
     *
     * @return T
     * @throws SQLException 异常
     */
    public <T> T executeQuery(String querySql, List<Object> params, ResultSetHandler<T> resultSetHandler) throws SQLException {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = this.getCurConnection();
            pst = conn.prepareStatement(querySql);
            if (params != null && params.size() > 0) {
                setParam(pst, params);
            }
            rs = pst.executeQuery();
            return resultSetHandler.handle(rs);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            close(rs);
            close(pst);
            closeConnection(conn);
        }
    }

    public <T> T executeQueryNoTran(String querySql, List<Object> params, ResultSetHandler<T> resultSetHandler) throws SQLException {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = this.getNewCurConnection();
            pst = conn.prepareStatement(querySql);
            if (params != null && !params.isEmpty()) {
                setParam(pst, params);
            }
            rs = pst.executeQuery();
            return resultSetHandler.handle(rs);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            close(rs);
            close(pst);
            closeConnection(conn);
        }
    }

    /**
     * 关闭预加载处理器
     */
    private void close(AutoCloseable closeable) {

        if (null != closeable) {
            try {
                closeable.close();
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        }
    }
    /**
     * 新建立的连接
     */
    private void closeConnection(Connection conn) {
        if(!DbTransactionContext.hasDbTransaction()){
            close(conn);
        }
    }


    /**
     * 设置参数
     *
     * @param pst    sql预加载对象
     * @param params 参数集合
     * @throws SQLException 异常
     */
    private void setParam(PreparedStatement pst, List<Object> params) throws SQLException {
        for (int index = 0; index < params.size(); index++) {
            Object param = params.get(index);
            this.paramContext.setParam(pst,index,param);
        }
    }

    public boolean execute(String sql) throws SQLException {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            if (log.isDebugEnabled()) {
                log.info(sql);
            }
            conn = this.getCurConnection();
            pst = conn.prepareStatement(sql);

            return pst.execute();
        } catch (SQLException exception) {
            exception.printStackTrace();
            throw exception;
        } finally {
            close(pst);
            closeConnection(conn);
        }
    }

}
